package com.xiaoyun.dao;
import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;
import javax.swing.Icon;
import javax.swing.ImageIcon;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;

/**
 * BaseDAO：封装了JDBC的常用CRUD操作
 *
 */
public class JDBCDAO implements Runnable{
//	ShowWait showWait = new ShowWait();  //为了远程数据库连接锁定界面加的。
	//waitFrame showWait = new waitFrame();
	Connection conn;
	Statement stmt;
	PreparedStatement ps;
	ResultSet rs;
	static String url, user, password, driver;

	public static void main(String[] args) {
		JDBCDAO j = new JDBCDAO();
		System.out.println(j.getConnection());
		
	}
	static {
		try {
			Properties pro = new Properties();
			pro.load(JDBCDAO.class.getResourceAsStream("/db.properties"));
			url = pro.getProperty("url");
			user = pro.getProperty("user");
			password = pro.getProperty("password");
			//driver = pro.getProperty("driver");

			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

/*	static {
		try {
			url="jdbc:mysql://103.253.145.186:3306/lxf?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10";
			user="lxf";
			password="l007xf";
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}*/
	
	public JDBCDAO() {
		//Thread t = new Thread(showWait);
		//t.start();
	}

	public Connection getConnection() {

		try {
	//		showWait.show("正在连接数据库。。。");
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		showWait.release();
		return conn;
	}

	/**
	 * 增删改
	 * @param sql
	 * @param params
	 * @return int
	 */
	public int update(String sql, Object[] params) {
		int rows = 0;
		try {
			getConnection();
	//		showWait.show("正在验证数据。。。");
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rows = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
//		showWait.release();
		return rows;
	}
	
	/**
	 * 查询多条记录
	 * @param sql
	 * @param params
	 * @return ArrayList<HashMap<String, Object>>
	 */
	public ArrayList<HashMap<String, Object>> findAll(String sql, Object[] params) {
		ArrayList<HashMap<String, Object>> list = null;
		try {
			getConnection();
	//		showWait.show("正在验证数据。。。");
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			list = new ArrayList<HashMap<String, Object>>();
			HashMap<String, Object> map = null;
			ResultSetMetaData md = rs.getMetaData();// 获取记录集的元数据
			int columnCount = md.getColumnCount();// 列数
			while (rs.next()) {
				map = new HashMap<String, Object>(columnCount);
				for (int i = 1; i <= columnCount; i++) {
					map.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
//		showWait.release();
		return list;

	}
	
	/**查询单条记录
	 * @param sql
	 * @param params
	 * @return HashMap<String, Object>
	 */
	public HashMap<String, Object> findOne(String sql, Object[] params) {
		HashMap<String, Object> obj = new HashMap<String, Object>();
		try {
			getConnection();
	//		showWait.show("正在验证数据。。。");
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			if(rs.next()){
				for (int i = 1; i <= columnCount; i++) {
					if(rs.getObject(i) instanceof BigDecimal){
						BigDecimal a=new BigDecimal(rs.getObject(i).toString());
						obj.put(md.getColumnName(i).toLowerCase(),a.intValue());
						//System.out.println("-----"+md.getColumnName(i).toLowerCase());
						//System.out.println("-----"+a.intValue());
						continue;
					}
					obj.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
	//	showWait.release();
		return obj;
	}
	
	/**
	 * 查询多条记录
	 * @param <T>
	 * @param sql
	 * @param params
	 * @return ArrayList<HashMap<String, Object>>
	 */
	public <T> ArrayList<T> findAll(String sql, Object[] params,Class<T> cls) {
		ArrayList<T> resultList = new ArrayList<T>();
		T resultObj = null;
		HashMap<String, Object> obj = new HashMap<String, Object>();
		try {
			getConnection();
	//		showWait.show("正在验证数据。。。");
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();// 获取记录集的元数据
			int columnCount = md.getColumnCount();// 列数
			while(rs.next()){
				resultObj = cls.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					if(rs.getObject(i) instanceof BigDecimal){
						BigDecimal a=new BigDecimal(rs.getObject(i).toString());
						obj.put(md.getColumnName(i).toLowerCase(),a.intValue());

					}else{
						obj.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
					}
					String colName = md.getColumnName(i).toLowerCase();
					Object colValue = rs.getObject(i);
					
					//获取bean的所有字段,并且绑定值
					Field field = cls.getDeclaredField(colName);
					field.setAccessible(true);
					
					field.set(resultObj, colValue);
				}
				resultList.add(resultObj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			close();
		}
	//	showWait.release();
		return resultList;
	}
	
	/**查询单条记录
	 * @param sql
	 * @param params
	 * @param cls
	 * @return <T>T
	 */
	public <T>T findOne(String sql, Object[] params, Class<T> cls) {
		T resultObj = null;
		HashMap<String, Object> obj = new HashMap<String, Object>();
		try {
			getConnection();
//			showWait.show("正在验证数据。。。");
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			if(rs.next()){
				resultObj = cls.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					obj.put(md.getColumnName(i), rs.getObject(i));
					String colName = md.getColumnName(i).toLowerCase();
					Object colValue = rs.getObject(i);
					
					//获取bean的所有字段,并且绑定值
					Field field = cls.getDeclaredField(colName);
					field.setAccessible(true);
					
					field.set(resultObj, colValue);
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
		return resultObj;
	}
	
	/**
	 * 多表查询返回ArrayList<Object[]>，结合ArrayList转二维数组的函数，用于表格显示。
	 * @param sql
	 * @param params
	 * @param column
	 * @return
	 */
	public ArrayList<Object[]> findAll(String sql, Object[] params, int column) {
		ArrayList<Object[]> result = new ArrayList<Object[]>();
		
		try {
			getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();// 获取记录集的元数据
			int columnCount = md.getColumnCount();// 列数
			//System.out.println("---------"+columnCount);
			while (rs.next()) {
				Object[] obj = new Object[column];
				for (int i = 1; i <= columnCount; i++) {
					obj[i-1]=rs.getObject(i);
				}
				result.add(obj);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close();
		}
	//	System.out.println(result.size()+"----1");
		return result;
	}
	
	/**
	 * 把arraylist 转换为二维数组。用于表格显示。
	 */
	public Object[][] ArrayListToArray(ArrayList<Object[]> arrayList){
		int row = arrayList.size();
		int com = arrayList.get(0).length;
		int i=0,j=0;
		Object[][] obj = new Object[row][com];
			for(i=0;i<row;i++){
				for(j=0;j<com;j++){
				obj[i][j]=arrayList.get(i)[j];
				}
			}
		return obj;
	}
	
	/**
	 * 关闭连接,释放资源
	 */
	
	public void close(){
		try {
			if(ps!=null) {
				ps.close();
				ps = null;
			}
			if(rs!=null) {
				rs.close();
				rs = null;
			}
			if(conn!=null) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public void run() {
		// TODO Auto-generated method stub
		
	}
}

/*
*//**
 * 为了远程数据库连接锁定界面，加的等待提示图。
 * @author uname
 *
 *//*
class ShowWait{   
	JFrame frame;
	public ShowWait() {
		
	}
	public void show(String title) {
		frame = new JFrame();
		frame.setBounds(500, 300, 320, 260);
		frame.setTitle(title);
		frame.setVisible(true);
		XFOnline.xfOnline.setEnabled(false);
		if(XFOnline.xfOnline.regframe!=null)
		XFOnline.xfOnline.regframe.setEnabled(false);
		if(XFOnline.xfOnline.loginframe!=null)
		XFOnline.xfOnline.loginframe.setEnabled(false);
		if(XFOnline.xfOnline.fixPersonDataframe!=null)
		XFOnline.xfOnline.fixPersonDataframe.setEnabled(false);
		if(XFOnline.xfOnline.msgListframe!=null)
		XFOnline.xfOnline.msgListframe.setEnabled(false);
	}
	public void release(){
		frame.setVisible(false);
		frame=null;
		XFOnline.xfOnline.setEnabled(true);
		if(XFOnline.xfOnline.regframe!=null)
		XFOnline.xfOnline.regframe.setEnabled(true);
		if(XFOnline.xfOnline.loginframe!=null)
		XFOnline.xfOnline.loginframe.setEnabled(true);
		if(XFOnline.xfOnline.fixPersonDataframe!=null)
		XFOnline.xfOnline.fixPersonDataframe.setEnabled(true);
		if(XFOnline.xfOnline.msgListframe!=null)
		XFOnline.xfOnline.msgListframe.setEnabled(true);
	}
}


*//**
 * 想在提示框中加入等待的gif图片，但是当数据库连接的时候显示的窗体却变空白了。即使调用了多线程，gif画面也会直接卡住。
 * 所以这里暂时放弃使用gif图片显示等待信息。
 * @author uname
 *
 *//*
class waitFrame extends JFrame implements Runnable{
	private JPanel contentPane;
	Icon icon;
	JLabel label;
	public waitFrame() {
		
	}
	public void run() {
		icon = new ImageIcon(getClass().getResource("wait.gif"));
		label = new JLabel("jhgj");
		label.setIcon(icon);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 450, 260);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		contentPane.setLayout(new BorderLayout(0, 0));
		setContentPane(contentPane);
		contentPane.add(label, BorderLayout.CENTER);
		// TODO Auto-generated method stub
		setVisible(true);
	}
	public void show(String title) {
        setVisible(true);
		
		//frame.waitFrame();
		//frame.setVisible(true);
		//frame.setBounds(100, 100, 321, 261);;
		//frame.setVisible(true);
		//frame.setEnabled(false);
		//frame.setTitle(title);
		//frame.setVisible(true);
		XFOnline.xfOnline.setEnabled(false);
		if(XFOnline.xfOnline.regframe!=null)
		XFOnline.xfOnline.regframe.setEnabled(false);
		if(XFOnline.xfOnline.loginframe!=null)
		XFOnline.xfOnline.loginframe.setEnabled(false);
		if(XFOnline.xfOnline.fixPersonDataframe!=null)
		XFOnline.xfOnline.fixPersonDataframe.setEnabled(false);
		if(XFOnline.xfOnline.msgListframe!=null)
		XFOnline.xfOnline.msgListframe.setEnabled(false);
	}
	
	public void release(){
		setVisible(true);
		//frame.setVisible(false);
		XFOnline.xfOnline.setEnabled(true);
		if(XFOnline.xfOnline.regframe!=null)
		XFOnline.xfOnline.regframe.setEnabled(true);
		if(XFOnline.xfOnline.loginframe!=null)
		XFOnline.xfOnline.loginframe.setEnabled(true);
		if(XFOnline.xfOnline.fixPersonDataframe!=null)
		XFOnline.xfOnline.fixPersonDataframe.setEnabled(true);
		if(XFOnline.xfOnline.msgListframe!=null)
		XFOnline.xfOnline.msgListframe.setEnabled(true);
	}
}
*/